Recycling Secondary Index Structures

Many database reorganization techniques move tuples in a table from one location to another in a single pass. For example, distributed database systems move or copy tables between sites to optimize data placement. However, such systems typically drop and then rebuild the secondary indices defined over the table being moved. There are two primary reasons for this. First, moving a table invalidates any physical tuple pointers contained in its secondary indices (e.g., in the leaves of a tree). Second, changes in tuple or page size can cause index tuples on the remote site to be repacked onto pages in a way that degrades the clustering imposed by the structure (e.g., in the upper levels of an R-tree). The cost of rebuilding secondary indices is largely why table movement has been considered a expensive operation. This, in turn, means that data layout optimization has been considered expensive as well. In this paper, we present a simple, efficient mechanism for translating index pointers as well as an approach to preserving internal index clustering. By exploiting the structure of the original index, we can recycle its important properties and produce a usable index on the remote site without the expense of building one from scratch. We also demonstrate the effectiveness of these mechanisms using performance measurements of an implementation in the Mariposa distributed data manager.